const mysql = require("mysql2");
const mysqlPromise = require("mysql2/promise");

// 使用连接池 + 模糊查询(因为%?% 会误认为是查询包含?)
(function () {
  // return
  //   return;
  const pool = mysqlPromise.createPool({
    host: "192.168.71.226",
    user: "root",
    password: "123456",
    database: "duyi",
    connectionLimit: 10,
    multipleStatements: true, // 运行多个sql语句
  });
  async function test(name) {
    let sql =
      "select * from employee where name like concat('%',?,'%') limit 0,2;";
    const [res, fields] = await pool.execute(sql, [name]);
    console.log(res);
  }

  test("袁");
})();

// 预编译sql语句, 使用变量的形式, 达到避免sql注入
(function () {
  return;
  async function test(id) {
    const connection = await mysqlPromise.createConnection({
      host: "192.168.71.226",
      user: "root",
      password: "123456",
      database: "duyi",
      connectionLimit: 10,
      multipleStatements: true, // 运行多个sql语句
    });
    let sql = "select * from company where id=?";
    const [res, fields] = await connection.execute(sql, [id]);
    console.log(res);
    connection.end();
  }

  test(4);
})();

(function () {
  return;

  async function test() {
    const connection = await mysqlPromise.createConnection({
      host: "192.168.71.226",
      user: "root",
      password: "123456",
      database: "duyi",
    });

    const [res, fields] = await connection.query(
      "insert into company(`name`,location,buildDate) values('abbc', '阿萨德', curdate());"
    );
    console.log(res);
    connection.end();
  }

  test();
})();

(function () {
  // return
  // 普通的链接
  // 创建一个普通数据库连接
  // const connection = mysql.createConnection({
  //   host: "192.168.71.226",
  //   user: "root",
  //   password: "123456",
  //   database: "duyi",
  // });
  // simple query
  // connection.query(
  //   "select * from employee limit 0,10",
  //   function (err, res, fields) {
  //     console.log("返回结果:", Array.isArray(res));
  //     // console.log(fields);
  //   }
  // );
  // connection.query(
  //   "insert into company(`name`,location,buildDate) values('abbc', '阿萨德', curdate());",
  //   function (err, res) {
  //     console.log("修改后", res);
  //   }
  // );
  // connection.query("delete from company where id=4;", function (err, res) {
  //   console.log("修改后", res);
  // });
})();
